import os
import pandas as pd
import openpyxl
import shutil
import re
import platform
import subprocess
from tqdm import tqdm
from concurrent.futures import ThreadPoolExecutor, as_completed
import threading

UNPROTECT_PASSWORD = None  # 如果有保护密码可以填在这里

def extract_number(filename):
    match = re.search(r'(\d+)', filename)
    return int(match.group(1)) if match else float('inf')

def unprotect_sheet(sheet):
    try:
        if UNPROTECT_PASSWORD:
            sheet.protection.set_password(UNPROTECT_PASSWORD)
        sheet.protection.sheet = False
    except Exception as e:
        print(f"⚠️ 无法撤销保护: {e}")

def merge_excel_group_fast(files_group, output_file_path):
    base_file = files_group[0]
    shutil.copyfile(base_file, output_file_path)

    wb = openpyxl.load_workbook(output_file_path)
    ws = wb.active
    unprotect_sheet(ws)

    last_row = ws.max_row

    all_dataframes = []
    for file in files_group[1:]:
        df = pd.read_excel(file, header=None, skiprows=6, engine='openpyxl')
        df.dropna(how='all', inplace=True)
        all_dataframes.append(df)

    if all_dataframes:
        combined_df = pd.concat(all_dataframes, ignore_index=True)

        for row in combined_df.itertuples(index=False):
            last_row += 1
            for col_index, value in enumerate(row, start=1):
                ws.cell(row=last_row, column=col_index, value=value)

    wb.save(output_file_path)
    return os.path.basename(output_file_path)

def play_sound():
    system = platform.system()
    try:
        if system == "Darwin":  # macOS
            subprocess.run(["afplay", "/System/Library/Sounds/Glass.aiff"])
        elif system == "Windows":
            import winsound
            winsound.MessageBeep()
    except Exception as e:
        print(f"⚠️ 播放音效失败: {e}")
lock = threading.Lock()  # 用于防止并发写同一文件
def merge_all_in_directory_fast(directory, max_workers=3):
    all_files = sorted([
        os.path.join(directory, f)
        for f in os.listdir(directory)
        if f.endswith(".xlsx") and not re.search(r"合并文件", f)
    ], key=lambda x: extract_number(os.path.basename(x)))

    if not all_files:
        print("⚠️ 没有找到 .xlsx 文件！")
        return

    # 每3个分成一组，且不重叠
    groups = [all_files[i:i+3] for i in range(0, len(all_files), 3)]
    valid_groups = [g for g in groups if len(g) >= 2]

    print(f"📂 共 {len(all_files)} 个文件，分成 {len(valid_groups)} 个有效组进行并发合并...\n")

    success_count = 0
    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        futures = []
        for group in valid_groups:
            start_num = extract_number(os.path.basename(group[0]))
            end_num = extract_number(os.path.basename(group[-1]))
            output_file_name = f"{start_num}-{end_num}合并文件.xlsx"
            output_file_path = os.path.join(directory, output_file_name)

            # 防止拷贝到自身
            if os.path.abspath(group[0]) == os.path.abspath(output_file_path):
                print(f"⚠️ 源文件和目标文件相同，跳过: {output_file_name}")
                continue

            futures.append(executor.submit(merge_excel_group_fast, group, output_file_path))

        for future in tqdm(as_completed(futures), total=len(futures), desc="🚀 合并中"):
            try:
                result = future.result()
                print(f"✅ 已生成: {result}")
                success_count += 1
            except Exception as e:
                print(f"❌ 合并失败: {e}")

    skipped = [g for g in groups if len(g) < 2]
    if skipped:
        print("\n⏭️ 以下组因文件数量不足被跳过：")
        for g in skipped:
            print(" -", [os.path.basename(f) for f in g])

    print(f"\n🎉 合并完成，总共生成 {success_count} 个合并文件！")
    play_sound()

if __name__ == "__main__":
    import sys

    try:
        if getattr(sys, 'frozen', False):  # 打包后运行
            base_dir = os.path.dirname(sys.executable)
        else:  # 脚本运行
            base_dir = os.path.dirname(os.path.abspath(__file__))

        os.chdir(base_dir)
        converted_dir = os.path.join(base_dir, 'converted')

        if not os.path.exists(converted_dir):
            print("❌ 未找到 'converted' 文件夹")
        else:
            merge_all_in_directory_fast(converted_dir, max_workers=5)

    except Exception as e:
        print(f"⚠️ 发生错误: {e}")
    
    input("按回车键退出...")  # 防止窗口关闭